// readers/oracle_reader.go
package readers

import (
	"context"
	"database/sql"
	"log"
	"mig2kwdb/common"
	"net"
	"strings"

	go_ora "github.com/sijms/go-ora/v2"
)

type OracleReader struct {
	db *sql.DB
}

// NewOracleReader 创建新的 OracleReader 实例并初始化数据库连接
func NewOracleReader(connInfo common.DBConnectionInfo) (*OracleReader, error) {
	// 构建 Oracle DSN
	// 注意：Oracle 的 DSN 格式可能因驱动而异，这里使用 go-ora 驱动的格式作为示例
	urlOptions := map[string]string{
		//"TRACE FILE":    traceFile,
		//"server":        servers,
		//"SSL":        "true",
		//"SSL Verify": "false",
		//"SID":        "orcl",
		//"wallet":        wallet,
		//"PREFETCH_ROWS": strconv.Itoa(prefetchRows),
		"LOB FETCH": "POST", //support BLOB
		"TIMEOUT":   "1800",
	}
	dsn := ""
	if connInfo.Method == 1 {
		urlOptions["SID"] = connInfo.InstanceName
		dsn = go_ora.BuildUrl(connInfo.Ip, connInfo.Port, "", connInfo.User, connInfo.Password, urlOptions)
	} else {
		dsn = go_ora.BuildUrl(connInfo.Ip, connInfo.Port, connInfo.InstanceName, connInfo.User, connInfo.Password, urlOptions)
	}

	db, err := sql.Open("oracle", dsn)
	if err != nil {
		return nil, err
	}

	return &OracleReader{
		db: db,
	}, nil
}
func (r *OracleReader) GetStartPos() (common.ReplicateStartPos, error) {
	return common.ReplicateStartPos{}, nil
}
func (r *OracleReader) StartReplicate(ctx context.Context, startPos common.ReplicateStartPos) error {
	return nil
}
func (r *OracleReader) GetObjectName(name string) string {
	escaped := strings.ReplaceAll(name, `"`, `\"`)
	return `"` + escaped + `"`
}
func (r *OracleReader) IsNetworkError(err error) bool {
	if err == nil {
		return false
	}

	// 尝试将错误转换为 net.Error
	if netErr, ok := err.(net.Error); ok {
		// 通过 net.Error 接口的方法判断
		if netErr.Timeout() {
			return true
		}
	}

	// 检查常见的网络错误字符串
	errMsg := err.Error()
	switch {
	case strings.Contains(errMsg, "connection refused"),
		strings.Contains(errMsg, "network is unreachable"),
		strings.Contains(errMsg, "connection reset by peer"),
		strings.Contains(errMsg, "io timeout"),
		strings.Contains(errMsg, "no such host"),
		strings.Contains(errMsg, "The Network Adapter could not establish the connection"):
		return true
	}

	return false
}

// GetDB 返回 Oracle 数据库连接
func (r *OracleReader) GetDB() *sql.DB {
	return r.db
}
func (r *OracleReader) GetDataBaseType() string {
	return "oracle"
}

// Close 关闭 Oracle 数据库连接
func (r *OracleReader) Close() {
	if r.db != nil {
		r.db.Close()
	}
}

// GetDataBases retrieves a list of all schemas (databases) in the Oracle instance
// In Oracle, "database" is often synonymous with "schema"
func (r *OracleReader) GetDataBases() ([]string, error) {
	// Oracle 没有直接的 "SHOW DATABASES" 命令，通常列出用户（模式）
	// 这里我们列出所有非系统用户作为数据库
	query := `
		SELECT username 
		FROM all_users 
		WHERE oracle_maintained = 'N'
		ORDER BY username
	`

	rows, err := r.db.Query(query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var databases []string
	for rows.Next() {
		var dbName string
		if err := rows.Scan(&dbName); err != nil {
			return nil, err
		}
		databases = append(databases, dbName)
	}

	if err = rows.Err(); err != nil {
		return nil, err
	}

	return databases, nil
}

// GetDatabaseTables retrieves a list of all tables in the specified schema (database)
func (r *OracleReader) GetDatabaseTables(databaseName, schemaName string) ([]string, error) {
	// 如果 schemaName 为空，则使用 databaseName
	// Oracle 中通常使用 schema (owner) 来区分表
	schema := schemaName
	if schema == "" {
		schema = databaseName
	}

	// 查询指定 schema 下的所有表
	query := `
		SELECT table_name 
		FROM all_tables 
		WHERE owner = UPPER(:1)
		ORDER BY table_name
	`

	rows, err := r.db.Query(query, schema)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var tables []string
	for rows.Next() {
		var tableName string
		if err := rows.Scan(&tableName); err != nil {
			return nil, err
		}
		tables = append(tables, tableName)
	}

	if err = rows.Err(); err != nil {
		return nil, err
	}

	return tables, nil
}
func (r *OracleReader) MapDataTypes(column []common.KwdbColumn) []common.KwdbColumn {
	for i := range column {
		switch strings.ToUpper(column[i].Type) {
		case "BINARY_DOUBLE", "BINARY_FLOAT":
			column[i].Type = common.DataTypes.FLOAT8.Name
		case "NUMBER":
			column[i].Type = common.DataTypes.DECIMAL.Name
		case "VARCHAR2":
			column[i].Type = common.DataTypes.VARCHAR.Name
		case "NVARCHAR2":
			column[i].Type = common.DataTypes.NVARCHAR.Name
		case "LONG", "NCLOB":
			column[i].Type = common.DataTypes.CLOB.Name
		case "RAW", "ROWID", "BFILE":
			column[i].Type = common.DataTypes.BYTES.Name
		default:
		}
	}
	return column

}
func (r *OracleReader) GetTableDefinition(databaseName, schemaName, tableName string) (common.TableDefinition, error) {
	var err error
	var def common.TableDefinition
	def.DatabaseName = databaseName
	def.SchemaName = schemaName
	def.TableName = tableName
	def.Columns, err = r.GetTableColumn(databaseName, schemaName, tableName)
	if err != nil {
		log.Printf("GetTableColumn error: %v", err)
		return def, err
	}

	return def, nil
}

// GetTableIndexes retrieves index information for a specific table in an Oracle database
func (r *OracleReader) GetTableIndexes(databaseName, schemaName, tableName string) ([]common.IndexInfo, error) {
	// 如果 schemaName 为空，则使用 databaseName
	schema := schemaName
	if schema == "" {
		schema = databaseName
	}

	query := `
		SELECT 
			i.index_name,
			i.uniqueness,
			ic.column_position,
			ic.column_name,
			ic.descend,
			CASE 
				WHEN EXISTS (
					SELECT 1 FROM all_constraints c
					WHERE c.constraint_name = i.index_name 
					AND c.owner = i.owner 
					AND c.constraint_type = 'P'
				) THEN 'YES'
				ELSE 'NO'
			END AS is_primary_key
		FROM all_indexes i
		INNER JOIN all_ind_columns ic ON i.index_name = ic.index_name AND i.owner = ic.index_owner
		WHERE i.table_name = UPPER(:1)
		  AND i.table_owner = UPPER(:2)
		ORDER BY i.index_name, ic.column_position
	`

	rows, err := r.db.Query(query, tableName, schema)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	// 使用 map 来组织索引信息
	indexMap := make(map[string]*common.IndexInfo)

	for rows.Next() {
		var indexName string
		var uniqueness string
		var columnSeq int
		var columnName string
		var descend string
		var isPrimary string

		err := rows.Scan(&indexName, &uniqueness, &columnSeq, &columnName, &descend, &isPrimary)
		if err != nil {
			return nil, err
		}

		// 如果索引不存在，创建新的索引信息
		if _, exists := indexMap[indexName]; !exists {
			isUnique := (uniqueness == "UNIQUE")
			isPrimaryKey := (isPrimary == "YES")

			indexInfo := &common.IndexInfo{
				Name:      indexName,
				IsUnique:  isUnique,
				IsPrimary: isPrimaryKey,
				Type:      "INDEX",
			}

			// 确定索引类型
			if isPrimaryKey {
				indexInfo.Type = "PRIMARY"
			} else if isUnique {
				indexInfo.Type = "UNIQUE"
			}

			indexMap[indexName] = indexInfo
		}

		// 添加列信息到索引中
		indexColumn := common.IndexColumn{
			Name:      columnName,
			Position:  columnSeq,
			SortOrder: "ASC",
		}

		// 根据排序规则确定排序顺序
		if descend == "DESC" {
			indexColumn.SortOrder = "DESC"
		}

		indexMap[indexName].Columns = append(indexMap[indexName].Columns, indexColumn)
	}

	if err = rows.Err(); err != nil {
		return nil, err
	}

	// 将 map 转换为 slice
	var indexes []common.IndexInfo
	for _, indexInfo := range indexMap {
		indexes = append(indexes, *indexInfo)
	}

	return indexes, nil
}

// GetTableColumn retrieves column information for a specific table in an Oracle database
func (r *OracleReader) GetTableColumn(databaseName, schemaName, tableName string) ([]common.KwdbColumn, error) {
	// 如果 schemaName 为空，则使用 databaseName
	schema := schemaName
	if schema == "" {
		schema = databaseName
	}

	// 使用更简单的查询方式，完全避免处理 LONG 类型
	query := `
		SELECT 
			a.column_name,
			a.column_id,
			a.data_type,
			CASE 
				WHEN a.char_length > 0 
				THEN a.char_length
				ELSE 0 
			END,
			COALESCE(a.data_precision, 0),
			CASE 
				WHEN a.data_scale IS NOT NULL 
				THEN a.data_scale
				ELSE 0 
			END,
			a.nullable,
			'', -- 不获取默认值以避免 LONG 类型问题
			CASE 
				WHEN EXISTS (
					SELECT 1 FROM all_cons_columns col_cons
					JOIN all_constraints cons ON col_cons.constraint_name = cons.constraint_name AND col_cons.owner = cons.owner
					WHERE cons.constraint_type = 'P'
					AND col_cons.table_name = a.table_name
					AND col_cons.column_name = a.column_name
					AND col_cons.owner = a.owner
				) THEN 'YES' 
				ELSE 'NO' 
			END,
			CASE 
				WHEN EXISTS (
					SELECT 1 FROM all_cons_columns col_cons
					JOIN all_constraints cons ON col_cons.constraint_name = cons.constraint_name AND col_cons.owner = cons.owner
					WHERE cons.constraint_type = 'U'
					AND col_cons.table_name = a.table_name
					AND col_cons.column_name = a.column_name
					AND col_cons.owner = a.owner
				) THEN 'YES' 
				ELSE 'NO' 
			END,
			COALESCE(acc.comments, ''),
			CASE 
				WHEN a.data_type = 'NUMBER' AND a.data_precision IS NULL AND a.data_scale = 0
				THEN 'YES' 
				ELSE 'NO' 
			END,
			'',
			''
		FROM all_tab_cols a
		LEFT JOIN all_col_comments acc ON a.table_name = acc.table_name AND a.column_name = acc.column_name AND a.owner = acc.owner
		WHERE a.table_name = UPPER(:1)
		  AND a.owner = UPPER(:2)
		  AND a.hidden_column = 'NO'
		ORDER BY a.column_id
	`

	rows, err := r.db.Query(query, tableName, schema)
	if err != nil {
		log.Println(query, err)
		return nil, err
	}
	defer rows.Close()

	var columns []common.KwdbColumn
	for rows.Next() {
		var col common.KwdbColumn
		err := rows.Scan(
			&col.Name,
			&col.Position,
			&col.Type,
			&col.Length,
			&col.Precision,
			&col.Scale,
			&col.Nullable,
			&col.DefaultValue,
			&col.PrimaryKey,
			&col.Unique,
			&col.Comment,
			&col.AutoIncrement,
			&col.Charset,
			&col.Collation,
		)
		if err != nil {
			return nil, err
		}
		columns = append(columns, col)
	}

	return r.MapDataTypes(columns), rows.Err()
}
func (r *OracleReader) SetDataBases(dataBases []common.DatabaseConfig) {

}
func (r *OracleReader) SetReplicateHandler(handler common.ReplicateHandlerInterface) {
}
func (r *OracleReader) GetConditionRows(databaseName, schemaName, tableName string, columns []common.KwdbColumn, condition string) (common.RowsInterface, error) {
	orderby := ""
	keyNum := 0
	sql := "select "
	for idx, col := range columns {
		if idx > 0 {
			sql += ","
		}
		sql += " " + r.GetObjectName(col.Name)
		if col.PrimaryKey.String == "YES" {
			if keyNum > 0 {
				orderby += ","
			}
			orderby += " " + r.GetObjectName(col.Name)
			keyNum++
		}
	}
	// 如果没有主键，则使用唯一索引
	if orderby == "" {
		keyNum = 0
		for _, col := range columns {
			if col.Unique.String == "YES" {
				if keyNum > 0 {
					orderby += ","
				}
				orderby += " `" + col.Name + "`"
				keyNum++
			}
		}
	}
	sql += " from " + r.GetObjectName(databaseName) + "." + r.GetObjectName(tableName)
	if condition != "" {
		sql += " where " + condition
	}
	if orderby != "" {
		sql += " order by " + orderby
	}

	rows, err := r.db.Query(sql)
	return common.WrapSQLRows(rows), err
}
